/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package workforce.limousine.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import limousine.vo.Driver;

/**
 *
 * @author krisada
 */
public class DriverService implements Service {

    public DataSource dataSource = null;
    private int limit = 20;

    public DriverService() {
        System.out.println("Initial driver");
    }

    public void print() {
        System.out.println("Hello World print.It 's Work.");
    }
    
    public int getLimit() {
        return limit;
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }

    public boolean save(Object obj) {
        try {
            Connection connection = dataSource.getConnection();
            System.out.println("Save driver");
            Driver o = (Driver) obj;
            int count = 0;
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO driver(DRIVER_ID, DRIVER_NAME, DRIVER_TYPE, DRIVER_RATE, PER_TYPE, ACCOUNT_NO, BANK, STATUS, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE) ");
            sql.append("VALUES(?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP,?,CURRENT_TIMESTAMP) ");
            PreparedStatement psmt = connection.prepareStatement(sql.toString());
            psmt.setString(1, o.getDriverId());
            psmt.setString(2,o.getDriverName());
            psmt.setInt(3, o.getDriverType());
            psmt.setInt(4, o.getDriverRate());
            psmt.setInt(5, o.getPerType());
            psmt.setString(6, o.getAccountNo());
            psmt.setString(7, o.getBank());
            psmt.setInt(8, o.getStatus());
            psmt.setString(9, o.getCreateBy());
            psmt.setString(10, o.getUpdateBy());
            count = psmt.executeUpdate();
            psmt.close();
            if (psmt != null) {
                psmt = null;
            }

            connection.close();
            if (connection != null) {
                connection = null;
            }
            return (count > 0) ? true : false;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public boolean update(Object obj) {
        try {
            Connection connection = dataSource.getConnection();
            System.out.println("Update Driver");
            Driver o = (Driver) obj;
            int count = 0;
            StringBuilder sql = new StringBuilder();
            sql.append("UPDATE driver ");
            sql.append("SET DRIVER_NAME=?,DRIVER_TYPE=?,DRIVER_RATE=?,PER_TYPE=?,ACCOUNT_NO=?,BANK=?, ");
            sql.append("STATUS=?,UPDATE_BY=?,UPDATE_DATE=CURRENT_TIMESTAMP ");
            sql.append("where DRIVER_ID=? ");
            PreparedStatement psmt = connection.prepareStatement(sql.toString());
            psmt.setString(1, o.getDriverName());
            psmt.setInt(2, o.getDriverType());
            psmt.setInt(3, o.getDriverRate());
            psmt.setInt(4, o.getPerType());
            psmt.setString(5, o.getAccountNo());
            psmt.setString(6, o.getBank());
            psmt.setInt(7, o.getStatus());
            psmt.setString(8, o.getUpdateBy());
            psmt.setString(9, o.getDriverId());
            count = psmt.executeUpdate();
            psmt.close();
            if (psmt != null) {
                psmt = null;
            }

            connection.close();
            if (connection != null) {
                connection = null;
            }
            return (count > 0) ? true : false;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public boolean remove(Object obj) {
        try {
            Connection conn = dataSource.getConnection();
            System.out.println("remove Driver");
            StringBuilder s = new StringBuilder();
            s.append("DELETE FROM driver WHERE DRIVER_ID = ?");
            System.out.println("sql[" + s.toString() + "]");
            PreparedStatement pstm = conn.prepareStatement(s.toString());
            pstm.setString(1, (String) obj);
            int ret = pstm.executeUpdate();
            pstm.close();
            if (pstm != null) {
                pstm = null;
            }

            conn.close();
            if (conn != null) {
                conn = null;
            }
            return (ret == 1) ? true : false;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public Object findById(String id) {
        try {
            Connection conn = dataSource.getConnection();
            System.out.println("FindById");
            StringBuilder s = new StringBuilder();
            s.append("SELECT ");
            s.append("IFNULL(A.DRIVER_ID, '-') AS DRIVER_ID,");
            s.append("IFNULL(A.DRIVER_NAME, '-') AS DRIVER_NAME,");
            s.append("IFNULL(A.DRIVER_TYPE, 0) AS DRIVER_TYPE,");
            s.append("IFNULL(A.DRIVER_RATE, 0) AS DRIVER_RATE,");
            s.append("IFNULL(A.PER_TYPE, 0) AS PER_TYPE,");
            s.append("IFNULL(A.ACCOUNT_NO, '-') AS ACCOUNT_NO,");
            s.append("IFNULL(A.BANK, '-') AS BANK,");
            s.append("IFNULL(A.STATUS, 0) AS STATUS,");
            s.append("IFNULL(A.CREATE_BY, '-') AS CREATE_BY,");
            s.append("IFNULL(A.CREATE_DATE, CURRENT_TIMESTAMP) AS CREATE_DATE,");
            s.append("IFNULL(A.UPDATE_BY, '-') AS UPDATE_BY,");
            s.append("IFNULL(A.UPDATE_DATE, CURRENT_TIMESTAMP) AS UPDATE_DATE, ");
            s.append("(SELECT IFNULL(B.LOOKUP_NAME,'-') FROM lookup B WHERE B.LOOKUP_VALUE = A.DRIVER_TYPE AND B.LOOKUP_GROUP_ID = 'DRIVER_TYPE' ) AS DRIVER_TYPE_NAME,");
            s.append("(SELECT IFNULL(B.LOOKUP_NAME, '-') FROM lookup B WHERE B.LOOKUP_VALUE = A.STATUS AND B.LOOKUP_GROUP_ID = 'STATUS' ) AS STATUS_NAME,");
            s.append("(SELECT IFNULL(B.USER_NAME, '-') FROM users B WHERE B.id = A.CREATE_BY ) AS CREATE_BY_NAME,");
            s.append("(SELECT IFNULL(B.USER_NAME, '-') FROM users B WHERE B.id = A.UPDATE_BY ) AS UPDATE_BY_NAME ");
            s.append("FROM driver A WHERE A.DRIVER_ID = ? ");
            System.out.println("sql[" + s.toString() + "]");
            PreparedStatement pstm = conn.prepareStatement(s.toString());
            pstm.setString(1,id);
            ResultSet rs = pstm.executeQuery();
            Driver o = new Driver();
            if (rs.next()) {
                System.out.println("-------- Object found ---------");
                o.setDriverId(rs.getString("DRIVER_ID"));
                o.setDriverName(rs.getString("DRIVER_NAME"));
                o.setDriverType(rs.getInt("DRIVER_TYPE"));
                o.setDriverRate(rs.getInt("DRIVER_RATE"));
                o.setPerType(rs.getInt("PER_TYPE"));
                o.setAccountNo(rs.getString("ACCOUNT_NO"));
                o.setBank(rs.getString("BANK"));
                o.setStatus(rs.getInt("STATUS"));
                o.setCreateBy(rs.getString("CREATE_BY"));
                o.setCreateDate(new Date(rs.getDate("CREATE_DATE").getTime()));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setUpdateDate(new Date(rs.getDate("UPDATE_DATE").getTime()));
                o.setDriverTypeName(rs.getString("DRIVER_TYPE_NAME"));
                o.setStatusName(rs.getString("STATUS_NAME"));
                o.setCreateByName(rs.getString("CREATE_BY_NAME"));
                o.setUpdateByName(rs.getString("UPDATE_BY_NAME"));
            }
            rs.close();
            if (rs != null) {
                rs = null;
            }

            pstm.close();
            if (pstm != null) {
                pstm = null;
            }

            conn.close();
            if (conn != null) {
                conn = null;
            }
            return o;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public List find(Object[] obj) {
        List<Driver> list = new ArrayList<Driver>();
        try {
            Connection connection = dataSource.getConnection();
            System.out.println("Save Driver");
            connection.close();
            return list;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return list;
    }

    public List<Driver> search(int offset, Object[] certiria) {
        List<Driver> list = new ArrayList<Driver>();
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            StringBuilder s = new StringBuilder();
            s.append("SELECT ");
            s.append("IFNULL(A.DRIVER_ID, '-') AS DRIVER_ID,");
            s.append("IFNULL(A.DRIVER_NAME, '-') AS DRIVER_NAME,");
            s.append("IFNULL(A.DRIVER_TYPE, 0) AS DRIVER_TYPE,");
            s.append("IFNULL(A.DRIVER_RATE, 0) AS DRIVER_RATE,");
            s.append("IFNULL(A.PER_TYPE, 0) AS PER_TYPE,");
            s.append("IFNULL(A.ACCOUNT_NO, '-') AS ACCOUNT_NO,");
            s.append("IFNULL(A.BANK, '-') AS BANK,");
            s.append("IFNULL(A.STATUS, 0) AS STATUS,");
            s.append("IFNULL(A.CREATE_BY, '-') AS CREATE_BY,");
            s.append("IFNULL(A.CREATE_DATE, CURRENT_TIMESTAMP) AS CREATE_DATE,");
            s.append("IFNULL(A.UPDATE_BY, '-') AS UPDATE_BY,");
            s.append("IFNULL(A.UPDATE_DATE, CURRENT_TIMESTAMP) AS UPDATE_DATE, ");
            s.append("IFNULL((SELECT IFNULL(B.LOOKUP_NAME,'-') FROM lookup B WHERE B.LOOKUP_VALUE = A.DRIVER_TYPE AND B.LOOKUP_GROUP_ID = 'DRIVER_TYPE' ), '-') AS DRIVER_TYPE_NAME,");
            s.append("IFNULL((SELECT IFNULL(B.LOOKUP_NAME, '-') FROM lookup B WHERE B.LOOKUP_VALUE = A.STATUS AND B.LOOKUP_GROUP_ID = 'STATUS' ), '-') AS STATUS_NAME,");
            s.append("IFNULL((SELECT IFNULL(B.USER_NAME, '-') FROM users B WHERE B.id = A.CREATE_BY ), '-') AS CREATE_BY_NAME,");
            s.append("IFNULL((SELECT IFNULL(B.USER_NAME, '-') FROM users B WHERE B.id = A.UPDATE_BY ), '-') AS UPDATE_BY_NAME ");
            s.append("FROM driver A WHERE A.DRIVER_NAME LIKE ? ");
            s.append("LIMIT ? OFFSET ?");
            System.out.println("sql[" + s.toString() + "]");
            PreparedStatement stmt = conn.prepareStatement(s.toString());
            String name = (String) certiria[0];
            name = (name != null) ? name.replace('*', '%') : "";
            if(name.indexOf("%")==-1){
                name = "%"+name+"%";
            }
            stmt.setString(1, name);
            stmt.setInt(2, limit);
            stmt.setInt(3, offset);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Driver o = new Driver();
                o.setDriverId(rs.getString("DRIVER_ID"));
                o.setDriverName(rs.getString("DRIVER_NAME"));
                o.setDriverType(rs.getInt("DRIVER_TYPE"));
                o.setDriverRate(rs.getInt("DRIVER_RATE"));
                o.setPerType(rs.getInt("PER_TYPE"));
                o.setAccountNo(rs.getString("ACCOUNT_NO"));
                o.setBank(rs.getString("BANK"));
                o.setStatus(rs.getInt("STATUS"));
                o.setCreateBy(rs.getString("CREATE_BY"));
                o.setCreateDate(new Date(rs.getDate("CREATE_DATE").getTime()));
                //o.setCreateDate(new Date());
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setUpdateDate(new Date(rs.getDate("UPDATE_DATE").getTime()));
                //o.setUpdateDate(new Date());
                o.setDriverTypeName(rs.getString("DRIVER_TYPE_NAME"));
                o.setStatusName(rs.getString("STATUS_NAME"));
                o.setCreateByName(rs.getString("CREATE_BY_NAME"));
                o.setUpdateByName(rs.getString("UPDATE_BY_NAME"));
                list.add(o);
            }
            rs.close();
            rs = null;
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                }
            }
        }
        return list;
    }

    public boolean removeAll(String[] ids) {
        try {
            Connection conn = dataSource.getConnection();
            System.out.println("Save Driver");
            StringBuilder s = new StringBuilder();
            s.append("DELETE FROM lookup WHERE TABLE_ID = ?");
            System.out.println("sql[" + s.toString() + "]");
            Statement pstm = conn.createStatement();
            for (int i = 0; i < ids.length; i++) {
                pstm.addBatch("DELETE FROM lookup WHERE TABLE_ID = '" + ids[i] + "'");
            }
            int ret[] = pstm.executeBatch();
            pstm.close();
            if (pstm != null) {
                pstm = null;
            }
            conn.close();
            if (conn != null) {
                conn = null;
            }
            return (ret.length == ids.length) ? true : false;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    public int searchCount(int offset, Object[] certiria) {
        int count = 0;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            StringBuilder s = new StringBuilder();
            s.append("SELECT COUNT(*) FROM driver WHERE DRIVER_NAME LIKE ?");
            System.out.println("sql[" + s.toString() + "]");
            PreparedStatement stmt = conn.prepareStatement(s.toString());
            String name = (String) certiria[0];
            name = (name != null) ? name.replace('*', '%') : "";
            stmt.setString(1, name);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
            rs.close();
            rs = null;
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                }
            }
        }
        return count;
    }

    public Object findById(String uid, String id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
    
    
}
